'''
Created on 2022/08/10

@author: zz
'''
import sys, os, re
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import PatternFill
import configparser

ini_file = '150-CanList-check.ini'
Color = ['ffff00', '00ccff']  # 黄、浅蓝
fille = PatternFill('solid', fgColor=Color[0])  # 设置填充颜色为 黄色
fille1 = PatternFill('solid', fgColor=Color[1])  # 设置填充颜色为 黄色

def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.create_sheet("sheet1")
    sheet = workbook.active
    # sheet = workbook.create_sheet(sheet_name)
    sheet.title = sheet_name
    for i in range(0, index):
        for j in range(0, len(value[i])):
            if i == 0:
                sheet.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille1
            else:    
                if str(value[i][j]).find("->") != -1 or value[i][j] == "Y":
                    sheet.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                else:
                    if value[i][j] and j == 13 and value[i][j] != 1:
                        sheet.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                    else:
                        sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
    workbook.save(path)
    print("创建xlsx格式表格写入数据成功!")

def addExcel(path, sheet, value):
    '''
	:param sheet:sheet的名称
    :param path:写入excel的路径
    :param value: 追加的数据
    :return:
    '''
    index = len(value)
    wb = openpyxl.load_workbook(path)
    wb.create_sheet(sheet)
    ws = wb[sheet]
    # for ss in value:
    #     ws.append(ss)
    for i in range(0, index):
        for j in range(0, len(value[i])):
            if i == 0:
                ws.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille1
            else:
                if str(value[i][j]).find("->") != -1 or value[i][j] == "Y":
                    ws.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                else:
                    if value[i][j] and j == 13 and value[i][j] != 1:
                        ws.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                    else:
                        ws.cell(row=i+1, column=j+1, value=str(value[i][j]))
    wb.save(path)
    print("xlsx格式新增sheet写入文件成功!")

def deleteExcel(path, sheet):
    delete_flag = False
    wb = openpyxl.load_workbook(path)
    for ws in wb.worksheets:
        #指定シートが存在していれば、変数にTrueを格納
        if ws.title == sheet:
            del wb[sheet]
            delete_flag = True
    if delete_flag:
        wb.save(path)
        print("xlsx格式删除sheet成功!")

def read_excel_xlsx(path, sheet_name, ba_columns, value):
    workbook = openpyxl.load_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook[sheet_name]
    print('获取工作表的大小:')
    print('总行数 -> ' + str(sheet.max_row))
    print('总列数 -> ' + str(sheet.max_column))

    columns=[""]*20
    for i in range(1,20):
        for j in range(1,sheet.max_column):
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[0].replace("\\n","\n"):
                columns[0] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[1].replace("\\n","\n"):
                columns[1] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[2].replace("\\n","\n"):
                columns[2] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[3].replace("\\n","\n"):
                columns[3] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[4].replace("\\n","\n"):
                columns[4] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[5].replace("\\n","\n"):
                columns[5] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[6].replace("\\n","\n"):
                columns[6] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[7].replace("\\n","\n"):
                columns[7] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[8].replace("\\n","\n"):
                columns[8] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[9].replace("\\n","\n"):
                columns[9] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[10].replace("\\n","\n"):
                columns[10] = get_column_letter(j)
    print(ba_columns.split(","))
    print (columns)
    # for row in sheet.rows:
    #     for cell in row:
    #         print(cell.value, "\t", end="")
    for i in range(7,sheet.max_row+1):
        j=i-7
        # value[j][0]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[0])).value
        # value[j][1]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[1])).value
        # value[j][2]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[2])).value
        # value[j][3]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[3])).value
        # value[j][4]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[4])).value
        # value[j][5]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[5])).value
        # value[j][6]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[6])).value
        # value[j][7]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[7])).value
        # value[j][8]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[8])).value
        # value[j][9]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[9])).value
        # value[j][10]=sheet.cell(row=i, column=column_index_from_string(columns.split(" ")[10])).value
        value[j][0]=sheet.cell(row=i, column=column_index_from_string(columns[0])).value
        value[j][1]=sheet.cell(row=i, column=column_index_from_string(columns[1])).value
        value[j][2]=sheet.cell(row=i, column=column_index_from_string(columns[2])).value
        value[j][3]=sheet.cell(row=i, column=column_index_from_string(columns[3])).value
        value[j][4]=sheet.cell(row=i, column=column_index_from_string(columns[4])).value
        value[j][5]=sheet.cell(row=i, column=column_index_from_string(columns[5])).value
        value[j][6]=sheet.cell(row=i, column=column_index_from_string(columns[6])).value
        value[j][7]=sheet.cell(row=i, column=column_index_from_string(columns[7])).value
        value[j][8]=sheet.cell(row=i, column=column_index_from_string(columns[8])).value
        value[j][9]=sheet.cell(row=i, column=column_index_from_string(columns[9])).value
        value[j][10]=sheet.cell(row=i, column=column_index_from_string(columns[10])).value

        if value[j][1] is None:
            value[j][1] = value[j-1][1]
            value[j][2] = value[j-1][2]

        # print('j -> ' + str(j))
        # print('CANID -> ' + str(value[j][1]))
        # print('Trans -> ' + str(value[j][10]))


if __name__ == '__main__':

    # input_csv = input('Please input CSV file directory = ').strip()
    # input_ba1 = input('Please input BA1 file directory = ').strip()
    # input_ba2 = input('Please input BA2 file directory = ').strip()
    # ini_file = input('Please input ini file directory = ').strip()

    # BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    BASE_DIR = os.path.abspath("./")
    # BASE_DIR = os.getcwd()
    print(BASE_DIR)
    # 创建配置文件对象
    con = configparser.ConfigParser()

    # 读取文件
    con.read(os.path.join(BASE_DIR,ini_file), encoding='utf-8')
    print(os.path.join(BASE_DIR,ini_file))
    # 获取所有section
    # sections = con.sections()
    # # ['url', 'email']

    # # 获取特定section
    # items = con.items('info') # 返回结果为元组

    # # 可以通过dict方法转换为字典
    # items = dict(items)

    input_csv = con.get('file','input_csv')
    input_ba1 = con.get('file','input_ba1')
    ba1_sheet = con.get('file','ba1_sheet')
    ba_columns = con.get('file','ba_columns')
    input_ba2 = con.get('file','input_ba2')
    ba2_sheet = con.get('file','ba2_sheet')
    # ba2_columns = con.get('file','ba2_columns')
    output_file = con.get('file','output_file')

    # print('ba1_columns -> ' + ba1_columns)
    # print('ba2_columns -> ' + ba2_columns)
    # print(ba_columns)
    # print(ba_columns.split(",")[1].replace("\\n","\n"))

    if not output_file.endswith(".xlsx"):
        print(f'{output_file} is not a Excel file.')
        sys.exit(1)

    file_date = [[""]*10 for i in range(3000)]
    file_csv = [[""]*2 for i in range(3000)]
    file_ba1 = [[""]*20 for i in range(20000)]
    file_ba2 = [[""]*20 for i in range(20000)]
    file_check = [[""]*20 for i in range(3000)]
    lines = []
    file_line = re.compile(r'.*\n')
    index = 0
    columns = 0

    if os.path.isfile(input_ba1):
        read_excel_xlsx(input_ba1, ba1_sheet, ba_columns, file_ba1)
    else:
        print(f'{input_ba1} is not a file or the file does not exist.')
        sys.exit(1)

    if os.path.isfile(input_ba2):
        read_excel_xlsx(input_ba2, ba2_sheet, ba_columns, file_ba2)
    else:
        print(f'{input_ba2} is not a file or the file does not exist.')
        sys.exit(1)

    if os.path.isfile(input_csv):
        # sheet_name = os.path.basename(input_csv).replace(".","_")
        # print(sheet_name)
        with open(input_csv, mode = 'r', encoding = 'utf-8', errors = 'ignore', newline = '\n') as fh_r:
            file_content = fh_r.read() + '\n'
            # linesカウント
            for m in re.finditer(file_line, file_content):
                lines.append(m.group())

        for line in lines:
            columns = 0
            # print(line)
            for date in line.replace("\n","").split("\t"):
                if date:
                    # print(date)
                    file_date[index][columns] = date
                columns += 1
            index += 1
        # if os.path.isfile(output_file):
        #     deleteExcel(output_file, sheet_name)
        #     addExcel(output_file, sheet_name, file_date)
        # else:
        #     write_excel_xlsx(output_file, sheet_name, file_date)
    else:
        print(f'{input_csv} is not a file or the file does not exist.')
        sys.exit(1)

    icsv = 0
    for i in range(0, index):
        idate = 0
        if i == 0:
            file_csv[icsv][0] = file_date[i+1][2]
            file_csv[icsv][1] = file_date[i+1][3]
            icsv += 1
        else:
            for j in range(0, icsv-1):
                if file_csv[j][0] == file_date[i+1][2]:
                    if file_csv[j][1].find(file_date[i+1][3]) == -1:
                        file_csv[j][1] = file_csv[j][1] + "," + file_date[i+1][3]
                        idate += 1

            if idate == 0:
                file_csv[icsv][0] = file_date[i+1][2]
                file_csv[icsv][1] = file_date[i+1][3]
                icsv += 1

    # print(index)   
    # print(icsv)
    # print(file_csv)

    file_check[0][0] = "Data Label"
    file_check[0][1] = "CAN ID"
    file_check[0][2] = "DLC"
    file_check[0][3] = "Data Pos."
    file_check[0][4] = "Data Length"
    file_check[0][5] = "+B"
    file_check[0][6] = "+BA"
    file_check[0][7] = "ACC"
    file_check[0][8] = "IGR"
    file_check[0][9] = "IGP"
    file_check[0][10] = "Trans. Initial Value"
    file_check[0][11] = "Changed"
    file_check[0][12] = "Unit"
    file_check[0][13] = "Count"

    icheck = 1
    for i in range(0, icsv-1):
        check1 = 0
        check2 = 0
        for ba1 in file_ba1:
            if ba1[0] and ba1[0] == file_csv[i][0]:
                check1 += 1
                file_check[icheck][0] = file_csv[i][0]
                file_check[icheck][1] = ba1[1]
                file_check[icheck][2] = ba1[2]
                file_check[icheck][3] = ba1[3]
                file_check[icheck][4] = ba1[4]
                file_check[icheck][5] = ba1[5]
                file_check[icheck][6] = ba1[6]
                file_check[icheck][7] = ba1[7]
                file_check[icheck][8] = ba1[8]
                file_check[icheck][9] = ba1[9]
                file_check[icheck][10] = ba1[10]
                file_check[icheck][11] = "N"
                file_check[icheck][12] = file_csv[i][1]
                file_check[icheck][13] = check1

        for ba2 in file_ba2:
            if ba2[0] and ba2[0] == file_csv[i][0]:
                check2 += 1
                if check1 == 0:
                    file_check[icheck][0] = file_csv[i][0]
                    file_check[icheck][1] = ba2[1]
                    file_check[icheck][2] = ba2[2]
                    file_check[icheck][3] = ba2[3]
                    file_check[icheck][4] = ba2[4]
                    file_check[icheck][5] = ba2[5]
                    file_check[icheck][6] = ba2[6]
                    file_check[icheck][7] = ba2[7]
                    file_check[icheck][8] = ba2[8]
                    file_check[icheck][9] = ba2[9]
                    file_check[icheck][10] = ba2[10]
                    file_check[icheck][11] = "N"
                    file_check[icheck][12] = file_csv[i][1]
                    file_check[icheck][13] = check2
                else:
                    if file_check[icheck][1] != ba2[1]:
                        file_check[icheck][1] = str(file_check[icheck][1]) + "->" + str(ba2[1])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][2] != ba2[2]:
                        file_check[icheck][2] = str(file_check[icheck][2]) + "->" + str(ba2[2])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][3] != ba2[3]:
                        file_check[icheck][3] = str(file_check[icheck][3]) + "->" + str(ba2[3])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][4] != ba2[4]:
                        file_check[icheck][4] = str(file_check[icheck][4]) + "->" + str(ba2[4])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][5] != ba2[5]:
                        file_check[icheck][5] = str(file_check[icheck][5]) + "->" + str(ba2[5])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][6] != ba2[6]:
                        file_check[icheck][6] = str(file_check[icheck][6]) + "->" + str(ba2[6])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][7] != ba2[7]:
                        file_check[icheck][7] = str(file_check[icheck][7]) + "->" + str(ba2[7])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][8] != ba2[8]:
                        file_check[icheck][8] = str(file_check[icheck][8]) + "->" + str(ba2[8])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][9] != ba2[9]:
                        file_check[icheck][9] = str(file_check[icheck][9]) + "->" + str(ba2[9])
                        file_check[icheck][11] = "Y"
                    if file_check[icheck][10] != ba2[10]:
                        file_check[icheck][10] = str(file_check[icheck][10]) + "->" + str(ba2[10])
                        file_check[icheck][11] = "Y"
        
        if check1 != 0 or check2 != 0:
            if check1 != 0 and check2 != 0:
                if check1 == check2:
                    file_check[icheck][13] = check1
                else:
                    file_check[icheck][13] = str(check1) + "->" + str(check2)
                    file_check[icheck][11] = "Y"
            icheck += 1
            
    sheet_name="150-CanList-check"
    if os.path.isfile(output_file):
        deleteExcel(output_file, sheet_name)
        addExcel(output_file, sheet_name, file_check)
    else:
        write_excel_xlsx(output_file, sheet_name, file_check)





